<?php

namespace Entities\Intelihelp;

use Doctrine\ORM\Query\Expr;
use Doctrine\ORM\Query;
use Doctrine\ORM\EntityRepository;

class TarefaRepository extends EntityRepository
{
	public function listar($params = array())
	{
		$qb		= $this->getEntityManager()->createQueryBuilder();
		$qb->select('t')
		->from('Entities\Intelihelp\Tarefa', 't')
		->innerJoin('t.status', 's')
		->innerJoin('t.equipe', 'e')
		->innerJoin('t.solicitante', 'sol')
		->leftJoin('e.pessoas', 'p');

		$where	= $qb->expr()->andX();
		$where->add($qb->expr()->eq(1,1));
		foreach ($params as $key => $value)
		{
			$where->add($qb->expr()->eq($key, $value));
		}
		$qb->where($where)
		->orderBy('t.dt_prazo', 'asc')
		->addOrderBy('t.dt_cadastro', 'asc');

		return $this->getEntityManager()->createQuery($qb->getDQL());
	}

	public function findStakeholders($tarefa)
	{
		$qb		= $this->getEntityManager()->createQueryBuilder();
		$qb->select('p.ds_nome, e.ds_email')
		->distinct()
		->from('\Entities\Corp\Pessoa', 'p')
		->from('\Entities\Intelihelp\Tarefa', 't')
		->leftJoin('t.anotacoes', 'a')
		->innerJoin('p.emails', 'e')
		->where("t.id_tarefa = '{$tarefa->getIdTarefa()}'
			AND (t.solicitante = p.id_pessoa OR t.responsavel = p.id_pessoa OR a.responsavel = p.id_pessoa)");

// 		echo '<pre>';print_r($qb->getQuery()->getSQL());die;
		return $qb->getQuery()->getResult(Query::HYDRATE_ARRAY);
	}

	public function tarefaPorStatus()
	{
		$qb		= $this->getEntityManager()->createQueryBuilder();
		$qb->select('count(t.id_tarefa) as qtd, s.ds_status')
		->from('\Entities\Intelihelp\Tarefa', 't')
		->innerJoin('t.status', 's')
		->groupBy('s.ds_status')
		->orderBy('s.ds_status');
		return $qb->getQuery()->getResult(Query::HYDRATE_ARRAY);
	}

	public function tarefaPrazo($aberta = true, $comparacaoPrazo = '=')
	{
		$qb		= $this->getEntityManager()->createQueryBuilder();
		$qb->select('count(t.id_tarefa) as qtd')
		->from('\Entities\Intelihelp\Tarefa', 't')
		->innerJoin('t.status', 's');
		$where		= $qb->expr()->andX();
		$dataAtual	= date('Y-m-d');

		if ($aberta)
			$where->add($qb->expr()->eq('s.fl_situacao',1));
		else
			$where->add($qb->expr()->eq('s.fl_situacao',0));

		if (!$aberta)
			$where->add("t.dt_prazo {$comparacaoPrazo} t.dt_finalizacao");
		else
			$where->add("t.dt_prazo {$comparacaoPrazo} '$dataAtual'");

		$qb->where($where);
// 		echo '<pre>';print_r($qb->getQuery()->getSQL());die;
		$retorno	= $qb->getQuery()->getResult(Query::HYDRATE_ARRAY);
		if (isset($retorno[0]['qtd']))
			return $retorno[0]['qtd'];
		else
			return 0;
	}

	public function qtdTarefa()
	{
		$qb		= $this->getEntityManager()->createQueryBuilder();
		$qb->select('count(t.id_tarefa) as qtd')
		->from('\Entities\Intelihelp\Tarefa', 't');
		$retorno	= $qb->getQuery()->getResult(Query::HYDRATE_ARRAY);
		return $retorno[0]['qtd'];
	}

	public function tarefaPorPeriodo($params = array())
	{
		$qb		= $this->getEntityManager()->createQueryBuilder();
		$qb->select('t.id_tarefa, t.dt_cadastro, t.dt_finalizacao')
		->from('\Entities\Intelihelp\Tarefa', 't');

		$where	= $qb->expr()->andX();
		$where->add($qb->expr()->eq(1,1));

		if (isset($params['dtinicial']) && isset($params['dtfinal']))
		{
			$dtinicial	= explode('/',$params['dtinicial']);
			$dtinicial	= $dtinicial[2].'-'.$dtinicial[1].'-'.$dtinicial[0];
			$dtfinal	= explode('/', $params['dtfinal']);
			$dtfinal	= $dtfinal[2].'-'.$dtfinal[1].'-'.$dtfinal[0];
// 			echo '<pre>';print_r($dtfinal);die;
			$where->add($qb->expr()->between('t.dt_cadastro',"'$dtinicial'", "'$dtfinal'"));
		}
		foreach ($params as $key => $value)
		{
			if ($key == 'dtinicial' || $key == 'dtfinal')
				continue;
			$where->add($qb->expr()->eq($key, $value));
		}
		$qb->where($where);
// 		->orderBy('s.dsstatus');
// echo '<pre>';print_r($qb->getQuery()->getSQL());die;
		return $qb->getQuery()->getResult(Query::HYDRATE_ARRAY);
	}
}